﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace yq.IBase.DBHelper.Paging
{
    internal class SQLPager : IPager
    {
        #region IPager 成员

        const string select = "select";
        const int selectLengh = 6;

        public System.Data.DataTable Paging(string sql,string orderby, int currentPageIndex, int pageSize, DbHelper hp)
        {
            string convsql1 = "";
            //当前条数
            int currentCount = ((currentPageIndex - 1) * pageSize);
            int endCount = currentCount + pageSize;

            if (currentCount >= 1)
            {
                currentCount = currentCount + 1;
            }


            if (string.IsNullOrEmpty(orderby))
            {
                convsql1 = "select * from (select row_number()over(order by __tc__)_rowNumber,* from ";
                sql = sql.Trim();
                int jq = sql.IndexOf(select, StringComparison.OrdinalIgnoreCase);
                sql = sql.Substring(jq + selectLengh);
                sql = "(select 0 __tc__," + sql + ")";
            }
            else
            {
                convsql1 = "select * from (select row_number()over(" + orderby + ")_rowNumber,* from ";
                sql = "(" + sql + ")";
            }

            string convsql2 = " pager1) pager2 where _rowNumber between " + currentCount + " and " + endCount + "";

           
            return hp.ExecuteDataTable(hp.GetSqlStringCommond(convsql1 + sql + convsql2));
        }

        //例句
        // select * from 
        //(
        //    select row_number()over(order by __tc__)_rowNumber,*
        //    from (select 0 __tc__,* from User_Log where ip='192.168.1.1') pager1
        //)pager2
        //where _rowNumber between 2 and 1; 

        public int CountPaging(string sql, DbHelper hp)
        {
            sql = sql.Trim();
            int jq = sql.IndexOf(select,  StringComparison.OrdinalIgnoreCase);
            sql = sql.Substring(jq + selectLengh);
            sql = "SELECT count(1) from ( select " + sql + ") t";
            object obj = hp.ExecuteScalar(hp.GetSqlStringCommond(sql));

            return Convert.ToInt32(obj);
        }

        #endregion


       
    }
}
